package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bean.UserBean;
import com.bean.UserBean;
import com.util.DBSource;

public class UserDao {

	DBSource db=new DBSource();
	Connection conn=null;
	PreparedStatement ps=null;
	
	//���
	public int addUser(UserBean bean){
		int rows=0;
		try {
		conn=db.getConn();
		String insert="insert into user (name,password,passwordVerify,tel,companyName,weiXinHao,email,address,post,userState,beginTime,endTime)" +
				"values(?,?,?,?,?,?,?,?,?,?,?,?)";
			
			ps=conn.prepareStatement(insert);
			
			ps.setString(1, bean.getName());
			ps.setString(2, bean.getPassword());
			ps.setString(3, bean.getPasswordVerify());
			ps.setString(4, bean.getTel());
			ps.setString(5, bean.getCompanyName());
			ps.setString(6, bean.getWeiXinHao());
			ps.setString(7, bean.getEmail());
			ps.setString(8, bean.getAddress());
			ps.setString(9, bean.getPost());
			ps.setString(10, bean.getUserState());
			ps.setString(11, bean.getBeginTime());
			ps.setString(12, bean.getEndTime());
			
			rows=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return rows;
	}
	//�޸�
	public int updateUser(UserBean bean){
		int rows=0;
		try {
		conn=db.getConn();
		String update="update user set name=?,password=?,passwordVerify=?,tel=?,companyName=?,weiXinHao=?," +
				"email=?,address=?,post=?,userState=?,beginTime=?,endTime=? where id=?";
		
			ps=conn.prepareStatement(update);
			
			ps.setString(1, bean.getName());
			ps.setString(2, bean.getPassword());
			ps.setString(3, bean.getPasswordVerify());
			ps.setString(4, bean.getTel());
			ps.setString(5, bean.getCompanyName());
			ps.setString(6, bean.getWeiXinHao());
			ps.setString(7, bean.getEmail());
			ps.setString(8, bean.getAddress());
			ps.setString(9, bean.getPost());
			ps.setString(10, bean.getUserState());
			ps.setString(11, bean.getBeginTime());
			ps.setString(12, bean.getEndTime());
			ps.setInt(13, bean.getId());
			
			rows=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return rows;
	}
	//ɾ��
	public int deleteUser(int id){
		int rows=0;
		try {
		conn=db.getConn();
		String delete="delete from user where id=?";
		
		ps=conn.prepareStatement(delete);
		ps.setInt(1, id);
		rows=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return rows;
	}
	//��ѯȫ��
	public List<UserBean> selectAll(){
		List<UserBean> users=new ArrayList<UserBean>();
		try {
		conn=db.getConn();
		String select="select * from user";
		
		ps=conn.prepareStatement(select);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			UserBean user=new UserBean();
			user.setId(rs.getInt("id"));
			user.setName(rs.getString("name"));
			user.setPassword(rs.getString("password"));
			user.setPasswordVerify(rs.getString("passwordVerify"));
			user.setTel(rs.getString("tel"));
			user.setCompanyName(rs.getString("companyName"));
			user.setWeiXinHao(rs.getString("weiXinHao"));
			user.setEmail(rs.getString("email"));
			user.setAddress(rs.getString("address"));
			user.setPost(rs.getString("post"));
			user.setUserState(rs.getString("userState"));
			user.setBeginTime(rs.getString("beginTime"));
			user.setEndTime(rs.getString("endTime"));
			users.add(user);
		}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return users;
	}
	//��ѯ����
	public UserBean login(String username,String password){
		UserBean user=null;
		try {
		conn=db.getConn();
		String select="select * from user where name=? and password=?";
		
		ps=conn.prepareStatement(select);
		ps.setString(1, username);
		ps.setString(2, password);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			user=new UserBean();
			user.setId(rs.getInt("id"));
			user.setName(rs.getString("name"));
			user.setPassword(rs.getString("password"));
			user.setPasswordVerify(rs.getString("passwordVerify"));
			user.setTel(rs.getString("tel"));
			user.setCompanyName(rs.getString("companyName"));
			user.setWeiXinHao(rs.getString("weiXinHao"));
			user.setEmail(rs.getString("email"));
			user.setAddress(rs.getString("address"));
			user.setPost(rs.getString("post"));
			user.setUserState(rs.getString("userState"));
			user.setBeginTime(rs.getString("beginTime"));
			user.setEndTime(rs.getString("endTime"));
		}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return user;
	}
	
	public UserBean loginE(String email,String password){
		UserBean user=null;
		try {
		conn=db.getConn();
		String select="select * from user where email=? and password=?";
		
		ps=conn.prepareStatement(select);
		ps.setString(1, email);
		ps.setString(2, password);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			user=new UserBean();
			user.setId(rs.getInt("id"));
			user.setName(rs.getString("name"));
			user.setPassword(rs.getString("password"));
			user.setPasswordVerify(rs.getString("passwordVerify"));
			user.setTel(rs.getString("tel"));
			user.setCompanyName(rs.getString("companyName"));
			user.setWeiXinHao(rs.getString("weiXinHao"));
			user.setEmail(rs.getString("email"));
			user.setAddress(rs.getString("address"));
			user.setPost(rs.getString("post"));
			user.setUserState(rs.getString("userState"));
			user.setBeginTime(rs.getString("beginTime"));
			user.setEndTime(rs.getString("endTime"));
		}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return user;
	}
	
	public UserBean loginTel(String tel,String password){
		UserBean user=null;
		try {
		conn=db.getConn();
		String select="select * from user where tel=? and password=?";
		
		ps=conn.prepareStatement(select);
		ps.setString(1, tel);
		ps.setString(2, password);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			user=new UserBean();
			user.setId(rs.getInt("id"));
			user.setName(rs.getString("name"));
			user.setPassword(rs.getString("password"));
			user.setPasswordVerify(rs.getString("passwordVerify"));
			user.setTel(rs.getString("tel"));
			user.setCompanyName(rs.getString("companyName"));
			user.setWeiXinHao(rs.getString("weiXinHao"));
			user.setEmail(rs.getString("email"));
			user.setAddress(rs.getString("address"));
			user.setPost(rs.getString("post"));
			user.setUserState(rs.getString("userState"));
			user.setBeginTime(rs.getString("beginTime"));
			user.setEndTime(rs.getString("endTime"));
		}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return user;
	}
	
	public UserBean selectById(int id){
		UserBean user=null;
		try {
		conn=db.getConn();
		String select="select * from user where id=?";
		
		ps=conn.prepareStatement(select);
		ps.setInt(1, id);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			user=new UserBean();
			user.setId(rs.getInt("id"));
			user.setName(rs.getString("name"));
			user.setPassword(rs.getString("password"));
			user.setPasswordVerify(rs.getString("passwordVerify"));
			user.setTel(rs.getString("tel"));
			user.setCompanyName(rs.getString("companyName"));
			user.setWeiXinHao(rs.getString("weiXinHao"));
			user.setEmail(rs.getString("email"));
			user.setAddress(rs.getString("address"));
			user.setPost(rs.getString("post"));
			user.setUserState(rs.getString("userState"));
			user.setBeginTime(rs.getString("beginTime"));
			user.setEndTime(rs.getString("endTime"));
		}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return user;
	}
	
	public List<UserBean> selectPages(int currentRows,int rowsPerPage){
		List<UserBean> list=new ArrayList<UserBean>();
		Connection conn=null;
		try{
			conn=db.getConn();
			String sql="select * from User limit ?,?";
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1,currentRows);
			ps.setInt(2,rowsPerPage);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				UserBean user=new UserBean();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setPasswordVerify(rs.getString("passwordVerify"));
				user.setTel(rs.getString("tel"));
				user.setCompanyName(rs.getString("companyName"));
				user.setWeiXinHao(rs.getString("weiXinHao"));
				user.setEmail(rs.getString("email"));
				user.setAddress(rs.getString("address"));
				user.setPost(rs.getString("post"));
				user.setUserState(rs.getString("userState"));
				user.setBeginTime(rs.getString("beginTime"));
				user.setEndTime(rs.getString("endTime"));
				list.add(user);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return list;
	}
}
